RE: High memory usage

>Date: Wed, 20 Jun 2001 23:06:56 -0500
>To: "Rainer Mager" <rmager@vgkk.com>
>From: Steven Lane <stevelcmc@mindspring.com>
>Subject: RE: High memory usage
>Cc: pgsql-admin@postgresql.orgl
>Bcc:
>X-Attachments:
>
>>Hi,
>>
>>    Hmm, I'm not sure I understand how I could use EXISTS in my query.
>>Can you
>>give me an example?
>>
>>Thanks,
>>
>>--Rainer
>>
>
>>> I hope this is not a massively stupid response, but creating a new
>>> self-join condition for each additional criterion seems a rather expensive
>>> approach. Can this be done more quickly using multiple EXISTS
>>> conditions to
>>> check for each of the specified criteria?
>>>
>>> -- sgl
>>>
>>>
>
>Something like
>
>SELECT DISTINCT product.product_id FROM product p
>WHERE
>EXISTS ( SELECT pr_prop_str.pr_property_id FROM pr_prop_str prs
>                  WHERE p.product_id = prs.product_id
>                  AND prs.pr_property_id = 147 AND prs.str = '3E362cb' )
>AND
>EXISTS ( SELECT pr_prop_str.pr_property_id FROM pr_prop_str prs
>                  WHERE p.product_id = prs.product_id
>                  AND prs.pr_property_id = 18 AND prs.str BETWEEN
>'000999999'  AND '004999999' )
>AND
>EXISTS ( SELECT pr_prop_str.pr_property_id FROM pr_prop_str prs
>                  WHERE p.product_id = prs.product_id
>                  AND prs.pr_property_id = 51 AND prs.str =
>'$Bi_(BO$B%&e_(BC~O$Be_(BC$B%&e_(BC~I$Be_(BC$B%)(B' )
>AND
>EXISTS ( SELECT pr_prop_str.pr_property_id FROM pr_prop_str prs
>                  WHERE p.product_id = prs.product_id
>                  AND prs.pr_property_id = 115 AND prs.str =1 )
>AND
>EXISTS ( SELECT pr_prop_str.pr_property_id FROM pr_prop_str prs
>                  WHERE p.product_id = prs.product_id
>                  AND prs.pr_property_id = 68 AND prs.str =5 )
>AND
>EXISTS ( SELECT pr_prop_str.pr_property_id FROM pr_prop_str prs
>                  WHERE p.product_id = prs.product_id
>                  AND prs.pr_property_id = 113 AND prs.str < '030001' )
>AND
>EXISTS ( SELECT pr_prop_str.pr_property_id FROM pr_prop_str prs
>                  WHERE p.product_id = prs.product_id
>                  AND prs.pr_property_id = 57 AND prs.str < '19980101' )
>AND
>EXISTS ( SELECT pr_prop_str.pr_property_id FROM pr_prop_str prs
>                  WHERE p.product_id = prs.product_id
>                  AND prs.pr_property_id = 158 AND prs.str=1 )
>
>
>was kind of what I had in mind.
>